
import pandas as pd

meta70 = pd.read_excel('meta/70行业名字.xlsx', index_col=0, sheet_name=None)

collector = {}

for key, col_df in meta70.items():
    k = key.replace('腾景70行业对应固定资产投资完成额', '')
    path = f"投资数据源\分行业_城镇固定资产投资完成额_{k}.xlsx"
    df = pd.read_excel(path, index_col=1)
    df = df.iloc[:,8:].T
    # 选取12月的累计值数据当作年度值
    df.index = pd.to_datetime(df.index)
    df.dropna(axis=0, how='all', inplace=True)

    data_dic = {}
    for row, se in col_df.iterrows():
        temp_cols = se.dropna().to_list()
        col_70_name = temp_cols[0]
        match_cols = temp_cols[1:]
        print(col_70_name, match_cols)
    
        data_dic[col_70_name] = df[match_cols].sum(axis=1)
    data_df = pd.DataFrame(data_dic)
    # data_df.index = data_df.index.year
    # data_df.index.name = 'year'
    collector[k] = data_df

with pd.ExcelWriter('temp_output/70行业_固定资产投资完成额.xlsx') as writer:
    for key, df in collector.items():
        df.to_excel(writer, sheet_name=key)
